﻿#include "OiPCH.hpp"
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlDriver>
#include <QtSql/QSqlError>
#include <QtSql/QSqlRecord>

namespace Oi
{
    namespace PLScannerCore
    {
        class DatabaseImpl : public Impl<Database>
        {
        public:
            void init();
            void exit();

        public:
            QSqlDatabase _db;
            QString _tb;
            QDateTime _lastTime;
        };

        void DatabaseImpl::init()
        {
            _tb = "plscannerData";

            _db = QSqlDatabase::addDatabase("QSQLITE");

            QSqlQuery query(_db);
            _db.setDatabaseName(PLScanner::instance()->localDataDir);

            _lastTime = QDateTime::currentDateTime();
            QDateTime ayearAgo = _lastTime.addYears(-1);

            QString deleteSql = QString("delete from '%1' where datetime < '%2'").arg(_tb).arg(ayearAgo.toString("yyyy-MM-dd hh:mm:ss"));
            query.exec(deleteSql);

            QSqlQuery reshape(_db);
            reshape.prepare("VACUUM");
            if (!reshape.exec())
            {
                //OiAlerter() << "Can't compress database!";
            }
        }
        
        void DatabaseImpl::exit()
        {
            _db.close();
        }

        Database::Database()
        {
            OI_I(Database)->init();
        }

        Database::~Database()
        {
            OI_E(Database)->exit();
        }

        QString Database::tableName() const
        {
            return OI_IMPL()->_tb;
        }

        bool Database::open()
        {
            OI_F(Database);
            bool ret = d->_db.open();
            if (ret)
            {
                QSqlQuery query(d->_db);
                QString sql = QString("select count(*) from sqlite_master where type = 'table' and name='%1'").arg(d->_tb);

                query.exec(sql);

                if (query.next() && query.value(0).toInt() <= 0)
                {
                    sql = QString("CREATE TABLE %1 (id INTEGER PRIMARY KEY AUTOINCREMENT,"
                        "result INTEGER,"
                        "datetime TEXT NOT NULL,"
                        "formula TEXT,"
                        "user TEXT,"
                        "fullHeight REAL,"
                        "waistHeight REAL,"
                        "endPointWidth REAL,"
                        "waistWidth REAL,"
                        "bottomWidth REAL)"
                       ).arg(d->_tb);

                    query.prepare(sql);
                    if(!query.exec())
                    {
                        OiWarning() << query.lastError();
                        OiWarning() << sql;
                        ret = false;
                    }
                    else
                    {
                        ret = true;
                    }
                }
                else
                {
                    QSqlRecord record = d->_db.record(d->_tb);

                    QStringList addCols = QStringList()
                        << "area";
                    for (int i = 0; i < addCols.size(); i++)
                    {
                        QString addStr = addCols[i];
                        if (!record.contains(addStr))
                        {
                            QString alterTable = QString("ALTER TABLE %1 ADD COLUMN %2 VARCHAR").arg(d->_tb).arg(addStr);

                            QSqlQuery alterQuery(d->_db);
                            if (!alterQuery.exec(alterTable))
                            {
                                OiWarning() << "add " + addStr + " col err.";
                            }
                        }
                    }

                    ret = true;
                }
            }
            else
            {
                OiWarning() << d->_db.lastError();
            }

            if (ret)
            {
                QSqlQuery query(d->_db);
                QString sql = QString("CREATE INDEX DateIndex1 on %1 (datetime, formula)").arg(d->_tb);
                query.exec(sql);
            }

            return ret;
        }

        bool Database::query(const QStringList keys, QMap<QString, QList<QPointF>>& data, const QDateTime from, const QDateTime end)
        {
            OI_F(Database);

            if (!d->_db.open())
            {
                return false;
            }

            QSqlQuery query(d->_db);
            query.prepare(QString("SELECT %1 FROM %2 WHERE datetime BETWEEN '%3' AND '%4'")
                .arg("datetime," + keys.join(','))
                .arg(d->_tb)
                .arg(from.toString("yyyy-MM-dd hh:mm:ss"))
                .arg(end.toString("yyyy-MM-dd hh:mm:ss")));

            query.exec();
            while (query.next())
            {
                qreal datetime = query.value(0).toDateTime().toMSecsSinceEpoch();

                int index = 1;
                for (auto& key : keys)
                {
                    data[key].append(QPointF(datetime, query.value(index).toDouble()));

                    index++;
                }
            }

            return true;
        }

        QStringList Database::group(const QString key, const QString db)
        {
            OI_F(Database);

            QStringList ret;

            if(!d->_db.open())
            {
                return ret;
            }

            QSqlQuery query(d->_db);
            query.prepare(QString("SELECT %1 FROM %2 GROUP BY %1")
                .arg(key)
                .arg(db));

            query.exec();

            while(query.next())
            {
                auto value = query.value(0).toString();

                if(!value.isEmpty())
                {
                    ret.push_back(query.value(0).toString());
                }
            }
            return ret;
        }

        bool Database::insert(const DataRow& data)
        {
            OI_F(Database);
            if(!d->_db.open())
            {
                return false;
            }

            QSqlQuery query(d->_db);
            
            QString queryString = QString("INSERT INTO %1 (result, datetime, formula, user, fullHeight, waistHeight, endPointWidth, waistWidth, bottomWidth, area) "
                "VALUES(%2, '%3', '%4', '%5', %6, %7, %8, %9, %10, %11)")
                .arg(d->_tb)
                .arg(data.result)
                .arg(data.datetime.toString("yyyy-MM-dd hh:mm:ss"))
                .arg(data.formula)
                .arg(data.user)
                .arg(data.fullHeight)
                .arg(data.waistHeight)
                .arg(data.endPointWidth)
                .arg(data.waistWidth)
                .arg(data.bottomWidth)
                .arg(data.area);

            query.prepare(queryString);
           
            bool ret = query.exec();

            if (!ret)
            {
                OiWarning() << query.lastError();
                OiWarning() << queryString;
            }

            return ret;
        }

        void Database::keepDatas(int days)
        {
            OI_F(Database);
            if (!d->_db.open())
            {
                return;
            }

            QString queryString = QString("delete FROM %1 WHERE dateTime < datetime('now', '-%2 days')").arg(d->_tb).arg(days);

            QSqlQuery query(d->_db);
            bool ret = query.exec(queryString);

            if (!ret)
            {
                OiWarning() << query.lastError();
                OiWarning() << queryString;
            }
        }

        Database* Database::instance()
        {
            static Database s_database;
            return &s_database;
        }

    }
}